#!/usr/bin/env python
# coding=utf-8
# __author__ = 'Yunchao Ling'

from pymongo import MongoClient
import pymysql


# 连接MongoDB
def InitMongoDB(MONGODB_HOST, MONGODB_PORT, MONGODB_DB_NAME, MONGODB_COLLECTION_NAME):
    client = MongoClient(MONGODB_HOST, MONGODB_PORT)
    db = client[MONGODB_DB_NAME]
    collection = db[MONGODB_COLLECTION_NAME]
    return client, collection


# 关闭MongoDB
def CloseMongoDB(MongoDB_Connection):
    MongoDB_Connection.close()


# 连接MySQL
def InitMySQL(MySQL_HOST, MySQL_USER, MySQL_PASSWORD, MySQL_DB):
    connection = pymysql.connect(MySQL_HOST, MySQL_USER, MySQL_PASSWORD, MySQL_DB)
    return connection


# 关闭MySQL
def CloseMySQL(MySQL_Connection):
    MySQL_Connection.close()


def GetReference(outfilepath):
    # 初始化输出文件
    outfile = open(outfilepath, 'w')
    outfile.write("PMID\tDOI\tPMCID\tTXT\n")
    outfile.flush()

    # 初始化MongoDB
    client, collection = InitMongoDB("10.188.188.22", 27017, "Pre_Data", "imsg")

    # 初始化MySQL
    mysql_connection = pymysql.connect("10.188.188.22", "plosp", "plosp", "plosp")

    # 初始化查找计数
    count = 0

    results = collection.find()
    for result in results:
        if "Reference" in result:
            for item in result["Reference"]:
                writeline = ''
                if "DOI" in item:
                    if "PMID" in item:
                        writeline += item["PMID"] + "\t" + item["DOI"] + "\t"
                    else:
                        cursor = mysql_connection.cursor()
                        sql = "SELECT pmid FROM tb_dds_article_otherid WHERE other_id = '%s'" % (item["DOI"])
                        cursor.execute(sql)
                        result = cursor.fetchone()
                        if result != None:
                            count += 1
                            writeline += str(result[0]) + "\t" + item["DOI"] + "\t"
                        else:
                            writeline += "\t" + item["DOI"] + "\t"
                        cursor.close()
                else:
                    if "PMID" in item:
                        writeline += item["PMID"] + "\t\t"
                    else:
                        writeline += "\t\t"
                # if "PMID" in item:
                #     writeline += item["PMID"] + "\t"
                # else:
                #     writeline += "\t"
                if "PMCID" in item:
                    writeline += item["PMCID"] + "\t"
                else:
                    writeline += "\t"
                if "txt" in item:
                    writeline += item["txt"]
                outfile.write(writeline + "\n")
                outfile.flush()
    outfile.close()

    print(count)

    # 关闭MongoDB
    CloseMongoDB(client)

    # 关闭MySQL
    CloseMySQL(mysql_connection)


def getJournal(inputfilePath, outputfilePath):
    # 初始化MySQL
    mysql_connection = pymysql.connect("10.188.188.22", "plosp", "plosp", "plosp")

    # 初始化读写文件
    inputfile = open(inputfilePath, 'r')
    inputfile.readline()
    outputfile = open(outputfilePath, 'w')
    outputfile.write("PMID\tDOI\tPMCID\tTXT\tJOURNAL_ID\tISOAbbreviation\n")
    outputfile.flush()
    for line in inputfile:
        line = line.rstrip("\n")
        splitline = line.split("\t")
        if splitline[0] != "":
            cursor1 = mysql_connection.cursor()
            sql1 = "SELECT journal_id FROM tb_dds_article WHERE pmid = '%d'" % (int(splitline[0]))
            cursor1.execute(sql1)
            result1 = cursor1.fetchone()
            if result1 != None:
                journal_id = result1[0]
                cursor2 = mysql_connection.cursor()
                sql2 = "SELECT isoabbreviation FROM tb_dds_journal WHERE id = '%d'" % (journal_id)
                cursor2.execute(sql2)
                result2 = cursor2.fetchone()
                journal_title = result2[0]
                line += "\t" + str(journal_id) + "\t" + journal_title
                outputfile.write(line + "\n")
                outputfile.flush()
            else:
                line += "\t\t"
                outputfile.write(line + "\n")
                outputfile.flush()
        else:
            line += "\t\t"
            outputfile.write(line + "\n")
            outputfile.flush()
    inputfile.close()
    outputfile.close()

    # 关闭MySQL
    CloseMySQL(mysql_connection)


if __name__ == '__main__':
    # GetReference("D:/PMID_LIST.tsv")
    getJournal("D:/PMID_LIST.tsv", "D:/PMID_LIST_WITHJOURNAL.tsv")
